package com.qipay.mapper;

import java.util.List;

public class SpChannelSqlProvider extends com.qipay.baas.mapper.SpChannelSqlProvider {
	public String selectByChannelTypeAndStatusAndRuleAndSpId(Byte channelType,
															  Integer orderAmount,List<Long> spIds) {
		String spidsSql = spIds.toString().replaceAll("\\[", "(").replaceAll("\\]", ")");
		String selectSql = "SELECT * FROM ((SELECT "+
				" sc.id, sc.sp_id, sc.channel_type, sc.fee_recharge, sc.state, sc.update_date, "+
				"sc.create_date "+
				"FROM sp_channel sc INNER JOIN sp ON sc.sp_id=sp.id "+
				"LEFT JOIN sp_channel_rule scr ON sc.`sp_id`=scr.`sp_id` AND sc.`channel_type`=scr.`channel_type` "+
				"WHERE (sc.channel_type = %s AND sc.`state`=0 AND sp.`state`=0 AND sp.delete_flag=0 "+
				"AND scr.`min_order_amount` <= %s "+
				"AND scr.`max_order_amount` > %s "+
				"AND sc.sp_id in "+spidsSql+") or(sc.channel_type = %s AND sc.`state`=0 AND sp.`state`=0 AND sp.delete_flag=0 AND scr.`sp_id` is NULL AND sc.sp_id in "+spidsSql+") ORDER BY sc.update_date LIMIT 1) "+
				"UNION "+
				"(SELECT "+
				"sc.id, sc.sp_id, sc.channel_type, sc.fee_recharge, sc.state, sc.update_date, "+
				"sc.create_date "+
				"FROM sp_channel sc INNER JOIN sp ON sc.sp_id=sp.id "+
				"LEFT JOIN sp_channel_rule scr ON sc.`sp_id`=scr.`sp_id` AND sc.`channel_type`=scr.`channel_type` "+
				"WHERE sc.channel_type = %s AND sc.`state`=0 AND sp.`state`=0 "+
				"AND scr.`current_total_amount` >= %s "+
				"AND scr.`min_order_amount` <= %s "+
				"AND scr.`max_order_amount` > %s "+
				"AND CASE ADDTIME(scr.`min_time_stop`,scr.`min_time_stop_duration`) > '24:00:00' WHEN 1 "+
				"THEN (CURTIME() BETWEEN scr.`min_time_stop` AND ADDTIME(scr.`min_time_stop`,scr.`min_time_stop_duration`) "+
				"OR ADDTIME(CURTIME(),'24:00:00')BETWEEN scr.`min_time_stop` AND ADDTIME(scr.`min_time_stop`,scr.`min_time_stop_duration`)) "+
				"ELSE (CURTIME() BETWEEN scr.`min_time_stop` AND ADDTIME(scr.`min_time_stop`,scr.`min_time_stop_duration`)) END "+
				"LIMIT 1))T "+
				"LIMIT 1 ";

		return String.format(selectSql, channelType,orderAmount,orderAmount,channelType,channelType,orderAmount,orderAmount,orderAmount);
	}

	public String selectByChannelTypeAndSpId(Byte channelType, List<Long> spIds) {
		String spidsSql = spIds.toString().replaceAll("\\[", "(").replaceAll("\\]", ")");
		String selectSql = "SELECT sc.id, sc.sp_id, sc.channel_type, sc.fee_recharge, sc.state, sc.update_date, sc.create_date, sc.quota "+
				"FROM sp_channel sc INNER JOIN sp ON sc.sp_id=sp.id "+
				"WHERE sc.channel_type = %s AND sc.`state`=0 AND sp.`state`=0 AND sp.delete_flag=0 AND sc.sp_id in "+spidsSql;
		return String.format(selectSql, channelType);
	}

	/**
	 * 订单单笔限额，通道单日限额
	 * @param channelType
	 * @param orderAmount
	 * @param spIds
	 * @return
	 */
	public String selectByChannelTypeAndRuleAndSpId(Byte channelType,
															 Integer orderAmount,List<Long> spIds) {
		String spidsSql = spIds.toString().replaceAll("\\[", "(").replaceAll("\\]", ")");
		String selectSql = "SELECT "+
				" sc.id, sc.sp_id, sc.channel_type, sc.fee_recharge, sc.state, sc.update_date, "+
				"sc.create_date, sc.quota,sp.card_no as sp_callback_ip,sp.sp_user_name "+
				"FROM sp_channel sc INNER JOIN sp ON sc.sp_id=sp.id "+
				"LEFT JOIN sp_channel_rule scr ON sc.`sp_id`=scr.`sp_id` AND sc.`channel_type`=scr.`channel_type` "+
				"WHERE (sc.channel_type = %s AND sc.`state`=0 AND sp.`state`=0 AND sp.delete_flag=0  "+
				"AND scr.`current_total_amount` >= %s "+
				"AND scr.`min_order_amount` <= %s "+
				"AND scr.`max_order_amount` >= %s "+
				"AND sc.sp_id in "+spidsSql+") or(sc.channel_type = %s AND sc.`state`=0 AND sp.`state`=0  AND sp.delete_flag=0 AND scr.`sp_id` is NULL AND sc.sp_id in "+spidsSql+") order by id asc;"
				;

		return String.format(selectSql, channelType,orderAmount,orderAmount,orderAmount,channelType);
	}
}